package com.example.demo.common.lbsgeo.geo.mysql;



import com.example.demo.common.lbsgeo.geo.Geohash;
import com.example.demo.common.lbsgeo.geo.po.Person;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by li on 2016/4/22.
 */
public class Test {
	
	
    private final double PI = 3.14159265358979323;
    private final double R = 6371229;
    public final static String woman = "woman";
    public final static String man = "man";


    public static void main(String args[]) {
        Test test = new Test();
        //生成测试数据
//        test.insertLocations();
        double[] mylocation = {36.1052, 120.3800};
//        double[] mylocation = {36.1052, 170.3800};
        //70公里内
        long range = 70*1000;
        //限制条数
        int number=5;
        test.geo(mylocation, range, null, false,number);
    }

    /**
     * 附近的人搜索
     *
     * @param mylocation 我的位置经纬度
     * @param distance   距离范围(单位：米)
     * @param sex        性别（男：man,女woman）
     * @param isSort     是否按值排序
     * @param number 限制条数
     */
    public void geo(double[] mylocation, long distance, String sex, boolean isSort,int number) {
        try {
            long start = System.currentTimeMillis();
            System.out.println("查询开始...");
//            double range = getLongtRange(mylocation[0], mylocation[1], distance);
            double[] rectangle = getRectangle(mylocation[0], mylocation[1], distance);
            List<Person> personsList = search( rectangle, sex, isSort);
            System.out.println("mysql用时:" + (System.currentTimeMillis() - start) + "ms");
            Person[] persons = personsList.toArray(new Person[personsList.size()]);
            System.out.println("排序..." + persons.length);
            for (int i = 0; i < persons.length - 1; i++) {
                for (int j = 0; j < persons.length - 1 - i; j++) {
                    double distance1 = 0.0;
                    if (persons[j].getDistance() != 0.0) {
                        distance1 = persons[j].getDistance();
                    } else {
                        distance1 = Geohash.getDistance(mylocation[0], mylocation[1], persons[j].getLat(), persons[j].getLon());
                        persons[j].setDistance(Math.round(distance1));
                    }
                    double distance2 = 0.0;
                    if (persons[j + 1].getDistance() != 0.0) {
                        distance2 = persons[j + 1].getDistance();
                    } else {
                        distance2 = Geohash.getDistance(mylocation[0], mylocation[1], persons[j + 1].getLat(), persons[j + 1].getLon());
                        persons[j + 1].setDistance(Math.round(distance2));
                    }
                    if (distance1 > distance2) {
                        Person temp = persons[j];
                        persons[j] = persons[j + 1];
                        persons[j + 1] = temp;
                    }
                }
            }

            //显示结果
            for (int i = 0; i < persons.length; i++) {
                Person person = persons[i];
                if (person.getDistance() > distance||(i+1)>number) break;
                System.out.println(person);
            }
//            System.out.println(persons[persons.length - 1]);
            System.out.println("搜索完成，用时:" + (System.currentTimeMillis() - start) + "ms");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 插入测试数据
     */
    public void insertLocations() {
        try {
            long start = System.currentTimeMillis();
            Connection connection = JdbcUtil.getConnection();
            StringBuffer sb = new StringBuffer();
            sb.append("insert into locations(lat,lon,sex,sort)")
                    .append("values(?,?,?,?)");
            PreparedStatement preparedStatement = connection.prepareStatement(sb.toString());
            double lat = 36.1052;
            double lon = 170.3844;
            for (int i = 0; i < 1000; i++) {
                preparedStatement.setDouble(1, lat);
                preparedStatement.setDouble(2, lon);
                preparedStatement.setInt(3, (int) (System.currentTimeMillis() % 2));
                preparedStatement.setInt(4, (int) (System.currentTimeMillis() % 10));
                preparedStatement.addBatch();
//                lon -= 0.0001;
                lon -= 0.1;
            }
            preparedStatement.executeBatch();
            preparedStatement.close();
            connection.close();
            System.out.println("耗时:" + (System.currentTimeMillis() - start) + "ms");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据经纬度和距离返回一个矩形范围
     *
     * @param lng
     *            经度
     * @param lat
     *            纬度
     * @param distance
     *            距离(单位为米)
     * @return [lng1,lat1, lng2,lat2] 矩形的左下角(lng1,lat1)和右上角(lng2,lat2)
     */
    public static double[] getRectangle( double lat,double lng, long distance) {
        float delta = 111000;
        if (lng != 0 && lat != 0) {
            double lng1 = lng - distance
                    / Math.abs(Math.cos(Math.toRadians(lat)) * delta);
            double lng2 = lng + distance
                    / Math.abs(Math.cos(Math.toRadians(lat)) * delta);
            double lat1 = lat - (distance / delta);
            double lat2 = lat + (distance / delta);
            return new double[] { lng1, lat1, lng2, lat2 };
        } else {
            // TODO ZHCH 等于0时的计算公式
            double lng1 = lng - distance / delta;
            double lng2 = lng + distance / delta;
            double lat1 = lat - (distance / delta);
            double lat2 = lat + (distance / delta);
            return new double[] { lng1, lat1, lng2, lat2 };
        }
    }

    /**
     * 根据距离得到经纬度差
     * @param lat1     纬度
     * @param longt1   经度
     * @param distance 距离范围
     * @return
     */
    public double getLongtRange(double lat1, double longt1, double distance) {
        double a = (180 * distance) / (PI * R * Math.cos(lat1 * PI / 180));
        return a;
    }
    /**
     * 搜索
     * @param range  距离范围
     * @param sex    性别
     * @param isSort 是否按值排序
     * @return
     * @throws Exception
     */
    public List<Person> search( double[] range, String sex, boolean isSort) {
        Connection connection = null;
        List<Person> persons = null;
        try {
            connection = JdbcUtil.getConnection();
            StringBuffer sb = new StringBuffer();
            sb.append("select * from locations")
                    .append(" where lat>").append(range[1])
                    .append(" and").append(" lat<").append(range[3])
                    .append(" and").append(" lon>").append(range[0])
                    .append(" and").append(" lon<").append(range[2]);
            if (sex != null) {
                if (sex.equalsIgnoreCase("man"))
                    sb.append(" and sex=0");
                else
                    sb.append(" and sex=1");
            }
            if (isSort) {
                sb.append(" order by sort");
            }
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sb.toString());
            System.out.println(sb);
            persons = new ArrayList<Person>();
            while (resultSet.next()) {
                String c1 = resultSet.getString(1);
                double c2 = resultSet.getDouble(2);
                double c3 = resultSet.getDouble(3);
                int c4 = resultSet.getInt(4);
                int c5 = resultSet.getInt(5);
                Person person = new Person();
                person.setId(c1);
                person.setLat(c2);
                person.setLon(c3);
                person.setSex(c4);
                person.setSort(c5);
                persons.add(person);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return persons;
    }
}
